﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Data.Common;
using Data.Enum;
using Data.Model;
using Data.ViewModel;

namespace DataAccess.AccountManage
{
    /// <summary>
    /// 业务类，主要是获取数据库中记录系统的业务操作（工单操作）的记录
    /// </summary>
    public class Businesses : DbBase, IBusinesses
    {
        /// <summary>
        /// 获取所办业务的报表
        /// </summary>
        /// <param name="pageIndex">页面标号</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public ResultInfo<PagingDataSet<Data.Model.Business>> GetBusiness(int pageIndex, int pageSize, DateTime beginTime, DateTime endTime)
        {
            var result = new ResultInfo<PagingDataSet<Data.Model.Business>> { ResultNumber = ResultEnum.Error };
            try
            {
                using (SqlCommand myCommand = GetProCommand())
                {
                    myCommand.CommandText = "Common_GetPagingRecords";
                    var fromClause = new StringBuilder();
                    fromClause.Append("business with (nolock) ");
                    var whereClause = new StringBuilder();
                    whereClause.Append(string.Format("operatetime between '{0}' and '{1}'", beginTime, endTime));

                    myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex - 1;
                    myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                    myCommand.Parameters.Add("@FromClause", SqlDbType.VarChar, 1024).Value = fromClause.ToString();
                    myCommand.Parameters.Add("@SelectFields", SqlDbType.VarChar, 512).Value = "*";
                    myCommand.Parameters.Add("@WhereClause", SqlDbType.NVarChar, 4000).Value = whereClause.ToString();
                    myCommand.Parameters.Add("@SortField", SqlDbType.VarChar, 256).Value = "id";
                    myCommand.Parameters.Add("@SortFieldIsDesc", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@MaxRecords", SqlDbType.Int).Value = int.MaxValue;
                    myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                    myCommand.Parameters.Add("@ReverseOrder", SqlDbType.Bit).Direction = ParameterDirection.Output;

                    var dataSet = new DataSet();
                    var da = new SqlDataAdapter(myCommand);
                    da.Fill(dataSet);

                    var list = dataSet.Tables[0].Rows.Cast<DataRow>().Select(dataRow => new Data.Model.Business(dataRow)).Where(temp => temp.TranByConstructFlag).ToList();

                    int totalRecords = Convert.ToInt32(myCommand.Parameters["@TotalRecords"].Value);
                    bool reverseOrder = Convert.ToBoolean(myCommand.Parameters["@ReverseOrder"].Value);

                    if (reverseOrder)
                        list.Reverse();

                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = new PagingDataSet<Data.Model.Business> { PageIndex = pageIndex, PageSize = pageSize, Records = list, TotalRecords = totalRecords };
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 获取业务的名称跟这个业务的数量，主要是前台生成统计图的时候用
        /// </summary>
        /// <param name="date">业务统计的时间</param>
        /// <returns></returns>
        public ResultInfo<List<businesscount>> GetBusinessCount(DateTime date)
        {
            var result = new ResultInfo<List<businesscount>>();
            try
            {
                using (SqlCommand command = GetProCommand())
                {
                    command.CommandText = "sp_GetBussinessCount";
                    command.Parameters.Add("@date", SqlDbType.DateTime).Value = date;

                    var ds = new DataSet();
                    var da = new SqlDataAdapter(command);
                    da.Fill(ds);

                    var counts = (from DataRow d in ds.Tables[0].Rows select new businesscount(d)).ToList();
                    result.Obj = counts;
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;
        }

        public ResultInfo<List<businesscount>> GetBusinessCount(DateTime startDate, DateTime endDate)
        {
            var result = new ResultInfo<List<businesscount>>();
            try
            {
                using (SqlCommand command = GetProCommand())
                {
                    command.CommandText = "sp_GetBussinessCount2";
                    command.Parameters.Add("@startdate", SqlDbType.DateTime).Value = startDate;
                    command.Parameters.Add("@enddate", SqlDbType.DateTime).Value = endDate;

                    var ds = new DataSet();
                    var da = new SqlDataAdapter(command);
                    da.Fill(ds);

                    var counts = (from DataRow d in ds.Tables[0].Rows select new businesscount(d)).ToList();
                    result.Obj = counts;
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;
        }

        /// <summary>
        /// 添加一条业务记录
        /// </summary>
        /// <param name="business">业务明细</param>
        /// <returns></returns>
        public int AddBusiness(Data.Model.Business business)
        {
            int result = -1;
            try
            {
                if (business != null)
                {
                    string sql =
                        string.Format("insert into Business([operateid],[businesstype],[businessname],[operatetime],[operateaddress],[operateaddressid],[operatepsamid],[operatetable],[operatetableid]) values({0},{1},'{2}',getdate(),'{3}',{4},'{5}','{6}',{7}) select @@identity id", business.operateid, ((int)business.businesstype), BusinessTypeName.GetBusinessTypeName((BusinessType)business.businesstype), business.operateaddress, business.operateaddressid, business.operatepsamid, business.operatetable, business.operatetableid);

                    DataSet dataSet = ExcuteSqlDataSet(sql);
                    if (dataSet != null)
                    {
                        result = Convert.ToInt32(dataSet.Tables[0].Rows[0]["id"]);
                    }
                    //CloseConn();
                }
            }
            catch (Exception)
            {
                result = -1;
            }

            return result;
        }

        /// <summary>
        /// 获取业务详细报表，参数为空就是忽略这项条件
        /// </summary>
        /// <param name="beginDate">统计开始时间</param>
        /// <param name="endDate">统计结束时间</param>
        /// <param name="operateid">需要统计的操作人员的id，就是统计这个人产生的工单</param>
        /// <param name="addressid">需要统计的停车点的id</param>
        /// <param name="businesstype">需要统计的业务的类型</param>
        /// <returns></returns>
        public ResultInfo<List<BusinessView>> GetBusinessReport(DateTime? beginDate, DateTime? endDate, long? operateid, long? addressid, int? businesstype)
        {
            ResultInfo<List<BusinessView>> result = new ResultInfo<List<BusinessView>>() { ResultNumber = ResultEnum.Error, Obj = new List<BusinessView>() };
            try
            {
                using (SqlCommand command = GetProCommand())
                {
                    command.CommandText = "GetBusinessReport";
                    command.Parameters.Add("@beginDateTime", SqlDbType.DateTime).Value = beginDate;
                    command.Parameters.Add("@endDateTime", SqlDbType.DateTime).Value = endDate;
                    command.Parameters.Add("@operateid", SqlDbType.BigInt).Value = operateid;
                    command.Parameters.Add("@operateAddressid", SqlDbType.BigInt).Value = addressid;
                    command.Parameters.Add("@businesstype", SqlDbType.Int).Value = businesstype;

                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    da.Fill(ds);

                    if (ds != null)
                    {
                        foreach (DataRow dataRow in ds.Tables[0].Rows)
                        {
                            BusinessView view = new BusinessView();
                            try
                            {
                                view.BusinessCount = 1;
                                view.BusinessMoney = dataRow["bmoney"] is DBNull
                                                         ? 0
                                                         : Converts.ConvertToDecimal(dataRow["bmoney"].ToString());
                                view.BusinessName = dataRow["businessname"] is DBNull
                                                        ? ""
                                                        : dataRow["businessname"].ToString();
                                view.CreatePerson = dataRow["username"] is DBNull
                                                        ? (dataRow["operateid"] is DBNull
                                                               ? ""
                                                               : dataRow["operateid"].ToString())
                                                        : dataRow["username"].ToString();
                                view.CreateTime = dataRow["operatetime"] is DBNull
                                                      ? DateTime.Now
                                                      : Convert.ToDateTime(dataRow["operatetime"]);
                                view.Id = dataRow["id"] is DBNull ? 0 : Convert.ToInt32(dataRow["id"]);
                                result.Obj.Add(view);
                            }
                            catch (Exception)
                            {

                            }
                        }
                        result.ResultNumber = ResultEnum.Success;
                    }
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;
        }

    }
}
